﻿Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_Etat_equipements
    Public num As Integer = -1
    Public numf As Integer = -1
    Public modif As Integer = 0
    Public Function qt_total()
        Dim a As Double = 0
        Dim b As Double = 0
        Dim c As Double = 0
        Try

            txt_Quantit_Total.Text = 0
            Cmd = New SqlCommand("select sum(Quantite) as sume from Equipement where Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            If Dr.HasRows = True Then
                b = Dr("sume")
            End If


            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        Try

            txt_Quantit_Total.Text = 0
            Cmd = New SqlCommand("select Quant_Manquant  from Equipement,Equipement_Etat where Equipement.Id_Equipement=Equipement_Etat.Id_Equipement and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()

            If Dr.HasRows = True Then
                Dr.Close()
                Cmd = New SqlCommand("select sum(Quant_Manquant) as sume from Equipement,Equipement_Etat where Equipement.Id_Equipement=Equipement_Etat.Id_Equipement and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "' and [Type]='" & Comb_type.SelectedItem.ToString & "'", Con)
                Dr = Cmd.ExecuteReader()
                Dr.Read()
                c = Dr("sume")
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        Return b - c
    End Function
    Public Sub vider()


        txt_Quantit_Manquant.Clear()
        txt_Quantit_Manquant.Text = 0

        txt_remarq.Clear()
        If Comb_Nom_Equipement.Items.Count > 0 Then
            Comb_Nom_Equipement.SelectedIndex = 0
        End If

        If Comb_type.Items.Count > 0 Then
            Comb_type.SelectedIndex = 0
        End If
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .Items.Count - 1
                    .Items(i).Selected = False
                    .Items(i).Focused = False
                Next
            End If
        End With
        modif = 0
    End Sub
    Public Sub affiche()
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If
        List_tous.Items.Clear()
        Try
            Cmd = New SqlCommand("select * from Equipement_Etat, Equipement where Equipement.Id_Equipement=Equipement_Etat.Id_Equipement and [Type] ='" & Comb_type.SelectedItem & "' and Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem & "'", Con)

            open()
            Dr = Cmd.ExecuteReader()

            Do While Dr.Read()

                Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Quant_Manquant"), Dr("Quant_rest"), Dr("date_Manquant"), Dr("Remarque"), Dr("id_eta")})
                List_tous.Items.Add(Line)

            Loop

            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Private Sub Frm_Etat_equipements_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            Comb_Nom_Equipement.Items.Clear()
            Cmd = New SqlCommand("Select distinct Nom_Equipement From Equipement", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()

                Comb_Nom_Equipement.Items.Add(Dr("Nom_Equipement"))

            Loop
            Dr.Close()
            cnClose()
            If Comb_Nom_Equipement.Items.Count > 0 Then
                Comb_Nom_Equipement.SelectedIndex = 0
            End If

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub Comb_Nom_Equipement_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_Nom_Equipement.SelectedIndexChanged
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If

        Try
            Comb_type.Items.Clear()
            Cmd = New SqlCommand("Select distinct Type From Equipement Where Nom_Equipement ='" & Comb_Nom_Equipement.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read
                    Comb_type.Items.Add(Dr("Type"))
                Loop
            End If

            Dr.Close()
            cnClose()

            If Comb_type.Items.Count > 0 Then
                Comb_type.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub Comb_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_type.SelectedIndexChanged
        txt_Quantit_Total.Text = qt_total()
    End Sub

    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click
        If Comb_Nom_Equipement.Text <> "" And Comb_type.Text <> "" And CDbl(txt_Quantit_Manquant.Text) <= CDbl(txt_Quantit_Total.Text) And txt_Quantit_Manquant.Text <> 0 Then
            If MsgBox("Vous Voulez Vraiment Ajouter ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter") = MsgBoxResult.Yes Then
                Try
                    Dim manq As Integer = CInt(txt_Quantit_Manquant.Text)
                    Dim total As Integer = CInt(txt_Quantit_Total.Text)
                    If manq > total Then
                        MsgBox("Changer Quantité Manquant !", MsgBoxStyle.Critical, "Réssayer !")
                        Exit Sub
                    End If
                    Try
                        Cmd = New SqlCommand("Select * From Equipement", Con)
                        open()
                        Dr = Cmd.ExecuteReader()
                        If Dr.HasRows Then
                            Do While Dr.Read()

                                If (Dr("Nom_Equipement") = Comb_Nom_Equipement.SelectedItem.ToString And Dr("Type") = Comb_type.SelectedItem.ToString) Then
                                    num = Dr("Id_Equipement")
                                End If
                            Loop
                        End If

                        Dr.Close()
                        cnClose()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try


                    Cmd = New SqlCommand("insert into Equipement_Etat (Id_Equipement,Quant_Manquant,date_Manquant,Remarque,Quant_rest)" & _
                    "values (@Id_Equipement,@Quant_Manquant,@date_Manquant,@Remarque,@Quant_rest)", Con)

                    Cmd.Parameters.Add("@Id_Equipement", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@Quant_Manquant", SqlDbType.Float).Value = txt_Quantit_Manquant.Text
                    Cmd.Parameters.Add("@date_Manquant", SqlDbType.Date).Value = Date_manq.Value.Date
                    Cmd.Parameters.Add("@Quant_rest", SqlDbType.Float).Value = txt_Quantité_rest.Text
                    Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()
                    MsgBox("Ajouté Avec Succes", MsgBoxStyle.Information, "Réussi !")
                    vider()
                    affiche()
                    txt_Quantit_Total.Text = qt_total()

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try
            End If
        End If
    End Sub

    Private Sub txt_Quantit_Manquant_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_Quantit_Manquant.TextChanged
        If txt_Quantit_Manquant.Text = "" Then
            txt_Quantit_Manquant.Text = 0
        End If


        Try
            Dim manq As Integer = CInt(txt_Quantit_Manquant.Text)
            Dim total As Integer = CInt(txt_Quantit_Total.Text)
            txt_Quantité_rest.Text = total - manq
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

    End Sub


    Private Sub txt_Quantit_Total_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_Quantit_Total.TextChanged
        txt_Quantit_Manquant.Text = 0
        affiche()
        Try

            Dim manq As Integer = CInt(txt_Quantit_Manquant.Text)
            Dim total As Integer = CInt(txt_Quantit_Total.Text)
            txt_Quantité_rest.Text = total - manq


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

    End Sub

    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click
        With List_tous
            Dim n As Integer
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                Next
                If MsgBox("Voulez Vous Vraiment Supprimer", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then



                    Try
                        Cmd = New SqlCommand("Delete From Equipement_Etat Where id_eta =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        modif = 0
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                        vider()
                        affiche()
                        txt_Quantit_Total.Text = qt_total()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try



                End If
            Else
                MsgBox("Vous n'avez selectionner aucun ligne !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With
    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged

        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(4).Text)
                    txt_Quantit_Manquant.Text = (.Items(.SelectedIndices(i)).SubItems(0).Text)
                    txt_Quantité_rest.Text = (.Items(.SelectedIndices(i)).SubItems(1).Text)
                    Date_manq.Text = (.Items(.SelectedIndices(i)).SubItems(2).Text)
                    txt_remarq.Text = (.Items(.SelectedIndices(i)).SubItems(3).Text)

                Next


            End If
        End With
    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click
        If modif <> 0 Then
            If Comb_Nom_Equipement.Text <> "" And Comb_type.Text <> "" Then
                If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then

                    Try
                        Cmd = New SqlCommand("Select * From Equipement", Con)
                        open()
                        Dr = Cmd.ExecuteReader()
                        If Dr.HasRows Then
                            Do While Dr.Read()

                                If (Dr("Nom_Equipement") = Comb_Nom_Equipement.SelectedItem.ToString And Dr("Type") = Comb_type.SelectedItem.ToString) Then
                                    num = Dr("Id_Equipement")
                                End If
                            Loop
                        End If

                        Dr.Close()
                        cnClose()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try

                    Try
                        Cmd = New SqlCommand("update Equipement_Etat set Id_Equipement=@Id_Equipement, Quant_Manquant=@Quant_Manquant,date_Manquant=@date_Manquant,Quant_rest=@Quant_rest,Remarque=@Remarque" & _
                        " Where id_eta=" & modif, Con)


                        Cmd.Parameters.Add("@Id_Equipement", SqlDbType.Int).Value = num
                        Cmd.Parameters.Add("@Quant_Manquant", SqlDbType.Float).Value = txt_Quantit_Manquant.Text
                        Cmd.Parameters.Add("@date_Manquant", SqlDbType.Date).Value = Date_manq.Value.Date
                        Cmd.Parameters.Add("@Quant_rest", SqlDbType.Float).Value = txt_Quantité_rest.Text
                        Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text



                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        modif = 0
                        MsgBox("Modification a Réussi ", MsgBoxStyle.Information, "Réussi !")
                        vider()
                        affiche()
                        txt_Quantit_Total.Text = qt_total()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            End If
        End If
    End Sub
End Class